﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using Hangfire.LiteDB;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.FastDBF;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.WebUtilities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Net.Http.Headers;
using MySql.Data.MySqlClient;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using Newtonsoft.Json;
using Npgsql;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Quartz.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Drawing.Printing;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Transactions;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 村庄管理服务
    /// </summary>
    public class CommonController : BaseController<VillageMngController>
    {
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsRepository;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;

        public CommonController(IStorageProvider storageProvider
            , IRepository<ZmUser> userRepository
            , IRepository<ZmBuildingsLayer> buildingsRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , ILogger<VillageMngController> logger
            , IRepository<ZmSystemLog> systemLogRepository) : base(logger, systemLogRepository)
        {
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();

            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _fileAttachRepository = fileAttachRepository;
            _villageFireSafetyRepository = villageFireSafetyRepository;
        }

        /// <summary>
        /// 根据村级行政区代码获取所有房屋列表
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("common/search-attr")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<BuildingPcYhLogDetailDto>), 200)]
        public RespRet<Page<object>> SearchAttrList2([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string txtKeyWord,  [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string floor)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }
            string xzqWhere = "";
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                xzqWhere = "  and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                xzqWhere = " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                xzqWhere = " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(floor) && floor!= "undefined")
            {
                if (floor != "3")
                {
                    sqlWhere += ( " and  cs in(" + floor + ")");
                }
                else 
                {
                    sqlWhere += ( " cs >=3 ");
                }
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }

            string sql = @"select ROW_NUMBER() OVER ()index,* from (select kk.*,
                    case when zc_create_on is not null   then '已自查' else '未自查'  end  zczt, 
                    case when jc_create_on is not null   then '已检查' else '未检查'  end  jczt,
					case when jz_create_on is not null   then '已核验' else '未核验'  end  jzzt,
					case when qj_create_on is not null   then '已核验' else '未核验'  end  qjzt,
					case when sj_create_on is not null   then '已核验' else '未核验'  end  sjzt
                    from (select mm.* , case when (jcfwzt is null and jzfwzt is null) then '正常出租'  else case when   (jcfwzt='1' OR jzfwzt='1') then '不出租' else  case when (jcfwzt='2' OR jzfwzt='2') then '已拆除' else '' end end
                      end fwzt ,jc_create_on,jz_create_on,qj_create_on,sj_create_on from (
                     select b1.fwbh, b1.xzqdm,b1.xzq,b1.town,b1.towndm,b1.village,b1.villagedm,b1.cs,
                    case when b2.address is null then b1.address else b2.address end address,
                    case when b2.hzxm is null then b1.hzxm else b2.hzxm end hzxm,
                    zc_create_on from (select* from zm_buildings where  ""CreateOn"" <='" + endTime + " 23:59:59' and fwbh not in(select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + "' )))  b1 " +
                    "  left join (select t1.fwbh,t1.hzxm,t1.address,t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') zc_create_on,t1.cs from zm_pc_base t1,zm_pc_yh t2 where t1.fwbh=t2.fwbh) b2 on b1.fwbh=b2.fwbh) mm " +
                    "  left join (select t1.fwzt jcfwzt,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') jc_create_on,t1.fwbh from zm_pc_base_cj t1,zm_pc_yh_cj t2 where t1.fwbh=t2.fwbh) pp on mm.fwbh=pp.fwbh" +
                    "  left join (select t1.fwzt jzfwzt,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') jz_create_on,t1.fwbh from zm_pc_base_sqjz t1 where yhly='4') jz on mm.fwbh=jz.fwbh" +
                    "  left join (select t1.fwzt qjfwzt,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') qj_create_on,t1.fwbh from zm_pc_base_sqjz t1 where yhly='3') qj on mm.fwbh=qj.fwbh" +
                    "  left join (select t1.fwzt sjfwzt,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') sj_create_on,t1.fwbh from zm_pc_base_sqjz t1 where yhly='2') sj on mm.fwbh=sj.fwbh" +
                    "  ) kk  order by xzqdm asc,villagedm,zc_create_on desc ) t  where 1=1 " + strWhere + xzqWhere+ sqlWhere + "  limit " + pageSize + " offset " + pageSize * (pageIndex-1);

            string sql_count = @"select count(1) cnt from (select kk.*,
                    case when zc_create_on is not null   then '已自查' else '未自查'  end  zczt, 
                    case when jc_create_on is not null   then '已检查' else '未检查'  end  jczt,
					case when jz_create_on is not null   then '已核验' else '未核验'  end  jzzt,
					case when qj_create_on is not null   then '已核验' else '未核验'  end  qjzt,
					case when sj_create_on is not null   then '已核验' else '未核验'  end  sjzt
                    from (select mm.* , case when (jcfwzt is null and jzfwzt is null) then '正常出租'  else case when   (jcfwzt='1' OR jzfwzt='1') then '不出租' else  case when (jcfwzt='2' OR jzfwzt='2') then '已拆除' else '' end end
                      end fwzt ,jc_create_on,jz_create_on,qj_create_on,sj_create_on from (
                     select b1.fwbh, b1.xzqdm,b1.xzq,b1.town,b1.towndm,b1.village,b1.villagedm,b1.cs,
                    case when b2.address is null then b1.address else b2.address end address,
                    case when b2.hzxm is null then b1.hzxm else b2.hzxm end hzxm,
                    zc_create_on from (select* from zm_buildings where  ""CreateOn"" <='" + endTime + " 23:59:59' and fwbh not in( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + "' )))  b1 " +
                    "  left join (select t1.fwbh,t1.hzxm,t1.address,t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,to_char(t2.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') zc_create_on,t1.cs from zm_pc_base t1,zm_pc_yh t2 where t1.fwbh=t2.fwbh) b2 on b1.fwbh=b2.fwbh) mm " +
                    "  left join (select t1.fwzt jcfwzt,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') jc_create_on,t1.fwbh from zm_pc_base_cj t1,zm_pc_yh_cj t2 where t1.fwbh=t2.fwbh) pp on mm.fwbh=pp.fwbh" +
                    "  left join (select t1.fwzt jzfwzt,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') jz_create_on,t1.fwbh from zm_pc_base_sqjz t1 where yhly='4') jz on mm.fwbh=jz.fwbh" +
                    "  left join (select t1.fwzt qjfwzt,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') qj_create_on,t1.fwbh from zm_pc_base_sqjz t1 where yhly='3') qj on mm.fwbh=qj.fwbh" +
                    "  left join (select t1.fwzt sjfwzt,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') sj_create_on,t1.fwbh from zm_pc_base_sqjz t1 where yhly='2') sj on mm.fwbh=sj.fwbh" +
                    "  ) kk  order by xzqdm asc,villagedm,zc_create_on desc ) t  where 1=1 " + strWhere+strWhere + xzqWhere + sqlWhere;



            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            var pageData = new Page<object>(list
                , pageIndex + 1, pageSize, count);
            return new RespRet<Page<object>>(true, "Ok", pageData);
        }

        /// <summary>
        /// 根据某个房屋编号获取最新的房屋基本信息
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("common/hourseinfo")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<HourseBaseInfo>), 200)]
        public RespRet<HourseBaseInfo> hourseinfo([FromQuery] string fwbh)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<HourseBaseInfo>(false, "user not logined");
            }

            HourseBaseInfo model = new HourseBaseInfo();
            string sql = "  select *,case when (coalesce(yhnum,0) >0) then  CAST('21' AS INTEGER) else  CAST('20' AS INTEGER)  end AS pczt from " +
                "( select b1.*,yhnum from (select t1.id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx," +
                "gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end jsrysstd,sstdjsqk,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                "zxqlyhsyqsl,sign,notes,t1.tdsfdb,t1.hzxm create_name,t1.create_on from zm_pc_base t1, zm_pc_yh t2 where t1.id = t2.base_id ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog t1 ,  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                "from zm_pc_yhlog_dx  group by yhlog_id) t2 where t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where fwbh ='" + fwbh + "'";
            var item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();
            model.BuildingPcYhLogDetailDtoList = item;
            return new RespRet<HourseBaseInfo>(true, "Ok", model);
        }

        /// <summary>
        /// 根据核验人员获取其核验房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("common/HcHourseList")]
        public RespRet<Page<object>> HcHourseList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageIndex, [FromQuery] int pagesize, [FromQuery] string endTime, [FromQuery] string startTime, [FromQuery] string txtKeyWord, [FromQuery] string roletype, [FromQuery] string nickname, [FromQuery] string cs, [FromQuery] string jsrysstd, [FromQuery] string floor)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string where = "";
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                where += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%'  or nickname like '%" + txtKeyWord + "%')";
            }

            string strWhere = "  ";
            string sqlWhere = "";
            if (model.RegionCode != "110" && model.RegionCode != "110000")
            {
                strWhere += " and xzqdm='" + model.RegionCode + "' ";
                sqlWhere += " and m1.xzqdm='" + model.RegionCode + "' ";
            }

            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
                sqlWhere += " and m1.xzqdm='" + xzqdm + "' and m1.town='" + towndm + "' and m1.village='" + villagedm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "'";
                sqlWhere += " and m1.xzqdm='" + xzqdm + "' and m1.town='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
                sqlWhere += " and m1.xzqdm='" + xzqdm + "' ";
            }

            if (!string.IsNullOrEmpty(cs) && cs != "0")
            {
                where += " and  cs in (" + cs + ") ";
            }
            if (!string.IsNullOrEmpty(floor) && floor != "0")
            {
                where += " and  cs in (" + floor + ") ";
            }
            string yhly = "";
            if (!string.IsNullOrEmpty(roletype))
            {
                yhly = " and  yhly ='" + roletype + "' ";
            }
            string createname = "";
            if (!string.IsNullOrEmpty(nickname))
            {
                createname = " and  m1.create_name ='" + nickname + "' ";
                where += " and nickname='" + nickname + "'";
            }
            if (!string.IsNullOrEmpty(jsrysstd)) 
            {
                where += " and jsrysstd='" + jsrysstd + "'";
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = " ";
            sql = @" select ROW_NUMBER() OVER ()index ,*   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,jsrysstd,create_on,create_name,create_by, 
                 case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
              "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id " + yhly + createname + " and (m1.create_on) " +
              "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 " + yhly + " and (create_on) " +
              "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in( select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59'  )) ) " +
              " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1  " + where + "  limit " + pagesize + " offset " + pagesize * (pageIndex - 1);
            //   sql = "select ROW_NUMBER() OVER ()index ,* from (select * from temp1 order by create_name desc,village desc, create_on desc  limit 200 offset 0 )  t ";


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = " ";

            sql_count = @" select count(1) cnt   from(SELECT t.*,nickname  FROM(select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,hzxm,address,fwbh,cs,jsrysstd,create_on,create_name,create_by, 
                 case when yhly='3' then '区级核验' when yhly='2' then '市级核验'   else '乡镇核验' end yhly from  (select ROW_NUMBER() OVER(PARTITION BY m1.fwbh ORDER BY m1.create_on DESC) AS row_num, " +
                "  m1.* from zm_pc_baselog_sqjz m1 ,zm_pc_yhlog_sqjz m2 where m1.id=m2.base_id " + yhly + createname + " and (m1.create_on) " +
                "  between '" + startTime + "' and '" + endTime + " 23:59:59'  " + sqlWhere + ") t1  WHERE  row_num=1 " + yhly + " and (create_on) " +
                "  between '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + "  and fwbh not in(  select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59'  )) ) " +
                " t   left join zm_user_cj m  on t.create_by=m.\"number\"  order by village desc,t.create_on desc) t where 1=1  " + where;
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 根据层数获取其对应的房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("common/HouseFloorDetail")]
        public RespRet<Page<object>> HouseFloorDetail([FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked, [FromQuery] string floor,[FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
            string strWhere = " ";
            if (model != null)
            {
                if (user.RoleType == 3) //区级
                {
                    strWhere += " and t3.xzqdm='" + model.RegionCode + "'";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "" + startTime + "";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            
            string where = "";
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.towndm='" + towndm + "' and t3.villagedm='" + villagedm + "'";
                where = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') and  (villagedm='" + villagedm + "')";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' and t3.towndm='" + towndm + "' ";
                where = " and xzqdm='" + xzqdm + "' and (towndm='" + towndm + "') ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and t3.xzqdm='" + xzqdm + "' ";
                where = " and xzqdm='" + xzqdm + "' ";
            }


            if (!string.IsNullOrEmpty(floor)&&floor!= "undefined")
            {
                if (floor.Contains("3"))
                {
                    string[] arrr = floor.Split(',');
                    if (arrr.Length == 2)
                    {
                        string cssql = "";
                        for (int k = 0; k < arrr.Length; k++)
                        {
                            if (arrr[k] == "3")
                            {
                                cssql += "  cs >=3 OR ";
                            }
                            if (arrr[k] == "1")
                            {
                                cssql += "  cs=1 OR ";
                            }
                            if (arrr[k] == "2")
                            {
                                cssql += " cs =2 OR ";
                            }
                        }

                        where += " and (" + (cssql.Substring(0, cssql.Length - 3)) + ")";
                    }
                    else
                    {
                        where += " and cs >=" + floor;
                    }
                }
                else
                {
                    where += " and cs in(" + floor + ")";
                }
            }

            //
            where += " and fwbh in (select fwbh from zm_buildings where  \"CreateOn\" <= '" + endTime + " 23:59:59') ";



            if (!string.IsNullOrEmpty(txtKeyWord) )
            {
                where += " and (hzxm like '%"+ txtKeyWord + "%' OR address like '%"+ txtKeyWord + "%' OR fwbh like '%"+ txtKeyWord + "%') ";
            }
            if (cked == "true") 
            {
                where += " and villagedm in(select villagedm from  zdcmd) ";
            }
            string toDay = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
                if (Convert.ToDateTime(endTime) > DateTime.Now)
                {
                    table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                }
            }
            else
            {
                DateTime endData = Convert.ToDateTime(toDay);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            //if (startTime == "2024-01-01" && endTime == today)
            //{
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select t1.* from ( SELECT t3.* FROM(select  xzq,xzqdm,towndm,town,villagedm,village,hzxm,address,fwbh,cs,""CreateOn""  from zm_buildings  " +
                " WHERE \"CreateOn\" BETWEEN '" + startTime+"' and '"+endTime+" 23:59:59'    ) t3 "+
                "  WHERE t3.\"CreateOn\"  BETWEEN '" + startTime + "' and '"+endTime+ " 23:59:59' "+ strWhere + " " +
               "  AND t3.fwbh not in ("+ 
                " select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) ) order by village ) t1 )  t  where 1=1" + where + " limit " + pageSize + " offset(" + (pageIndex-1) + ") * " + pageSize;
            //}
            //else
            //{
            //    sql = @"select ROW_NUMBER() OVER ()index ,* from (select t1.* from ( SELECT t3.* FROM(SELECT   t1.ID,t1.xzq, t1.xzqdm,t1.towndm town,t1.town towndm,t1.villagedm village,t1.village villagedm,hzxm,address,fwbh, cs,create_on,xggdjsbp FROM zm_pc_baselog t1 " +
            //    " WHERE create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'    ) t3 " +
            //    " INNER JOIN zm_pc_yhlog m2 ON t3.ID = m2.base_id  WHERE t3.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + " " +
            //   "  AND t3.fwbh not in (" +
            //    " select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) ) order by village ) t1 )  t  where 1=1" + where + " limit " + pageSize + " offset(" + (pageIndex - 1) + ") * " + pageSize;
            //}
            string sql_count = "";
            //if (startTime == "2024-01-01" && endTime == today)
            //{
                sql_count = @"select count(1) cnt from (select t1.* from ( SELECT t3.* FROM(select  xzq,xzqdm,towndm,town,villagedm,village,hzxm,address,fwbh,cs,""CreateOn""  from zm_buildings  " +
                " WHERE \"CreateOn\" BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'    ) t3 " +
                "  WHERE t3.\"CreateOn\"  BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' " + strWhere + " " +
               "  AND t3.fwbh not in (" +
                " select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) ) order by village ) t1 )  t   where 1=1" + where ;
            //}
            //else
            //{
            //    sql_count = @"select count(1) cnt from (select t1.*  from ( SELECT t3.* FROM(SELECT   t1.ID,t1.xzq, t1.xzqdm,t1.towndm town,t1.town towndm,t1.villagedm village,t1.village villagedm,hzxm,address,fwbh, cs,create_on,xggdjsbp FROM zm_pc_baselog t1 " +
            //       " WHERE create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'    ) t3 " +
            //       " INNER JOIN zm_pc_yhlog m2 ON t3.ID = m2.base_id  WHERE t3.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59 ' " + strWhere + " " +
            //      "  AND t3.fwbh not in (" +
            //       " select DISTINCT fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) 	and (final_create_on = '" + endTime + " 23:59:59' ) ) order by village ) t1)  t  where 1=1" + where;

            //}

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        
    }
}